{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "from utils import *\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### FNDDS data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The most recent three years of FNDDS tables contain nutrition data. The column names are slightly different.\n",
    "df_1516 = pd.read_excel('../data/2015-2016 Ingredients.xlsx', skiprows=1)\n",
    "df_1718 = pd.read_excel('../data/2017-2018 Ingredients.xlsx', skiprows=1)\n",
    "df_1920 = pd.read_excel('../data/2019-2020 Ingredients.xlsx', skiprows=1)\n",
    "\n",
    "# Unify the column names.\n",
    "df_1516 = df_1516.rename(columns={'WWEIA Category code': 'WWEIA Category number'})\n",
    "\n",
    "# A small proportion of FNDDS data, such as code and descriptions change over the years.\n",
    "# Here we take the latest version of data if there are duplicates.\n",
    "df_fndds = pd.concat([df_1516, df_1718, df_1920])\n",
    "df_fndds = df_fndds[['Food code', 'Main food description', 'WWEIA Category number', 'WWEIA Category description', 'Ingredient code', 'Ingredient description']]\n",
    "df_fndds = df_fndds.drop_duplicates(subset=['Food code', 'WWEIA Category number', 'Ingredient code'], keep='last')\n",
    "df_fndds = df_fndds.sort_values(by='Food code')\n",
    "\n",
    "# This table records the connections between food and ingredients.\n",
    "df_fndds = df_fndds.rename(columns={'Food code': 'food_id', 'Main food description': 'food_desc', 'WWEIA Category number': 'WWEIA_id',\n",
    "                        'WWEIA Category description': 'WWEIA_desc', 'Ingredient code': 'ingredient_id', 'Ingredient description': 'ingredient_desc'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "9260\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>food_id</th>\n",
       "      <th>food_desc</th>\n",
       "      <th>WWEIA_id</th>\n",
       "      <th>WWEIA_desc</th>\n",
       "      <th>ingredient_id</th>\n",
       "      <th>ingredient_desc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11000000</td>\n",
       "      <td>Milk, human</td>\n",
       "      <td>9602</td>\n",
       "      <td>Human milk</td>\n",
       "      <td>1107</td>\n",
       "      <td>Milk, human, mature, fluid (For Reference Only)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11100000</td>\n",
       "      <td>Milk, NFS</td>\n",
       "      <td>1004</td>\n",
       "      <td>Milk, reduced fat</td>\n",
       "      <td>1085</td>\n",
       "      <td>Milk, nonfat, fluid, with added vitamin A and ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11100000</td>\n",
       "      <td>Milk, NFS</td>\n",
       "      <td>1004</td>\n",
       "      <td>Milk, reduced fat</td>\n",
       "      <td>1082</td>\n",
       "      <td>Milk, lowfat, fluid, 1% milkfat, with added vi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11100000</td>\n",
       "      <td>Milk, NFS</td>\n",
       "      <td>1004</td>\n",
       "      <td>Milk, reduced fat</td>\n",
       "      <td>1079</td>\n",
       "      <td>Milk, reduced fat, fluid, 2% milkfat, with add...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11100000</td>\n",
       "      <td>Milk, NFS</td>\n",
       "      <td>1004</td>\n",
       "      <td>Milk, reduced fat</td>\n",
       "      <td>1077</td>\n",
       "      <td>Milk, whole, 3.25% milkfat, with added vitamin D</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    food_id    food_desc  WWEIA_id         WWEIA_desc  ingredient_id  \\\n",
       "0  11000000  Milk, human      9602         Human milk           1107   \n",
       "4  11100000    Milk, NFS      1004  Milk, reduced fat           1085   \n",
       "3  11100000    Milk, NFS      1004  Milk, reduced fat           1082   \n",
       "2  11100000    Milk, NFS      1004  Milk, reduced fat           1079   \n",
       "1  11100000    Milk, NFS      1004  Milk, reduced fat           1077   \n",
       "\n",
       "                                     ingredient_desc  \n",
       "0    Milk, human, mature, fluid (For Reference Only)  \n",
       "4  Milk, nonfat, fluid, with added vitamin A and ...  \n",
       "3  Milk, lowfat, fluid, 1% milkfat, with added vi...  \n",
       "2  Milk, reduced fat, fluid, 2% milkfat, with add...  \n",
       "1   Milk, whole, 3.25% milkfat, with added vitamin D  "
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# There are 9260 foods in total.\n",
    "# This is the FNDDS dataset. \n",
    "print(len(set(df_fndds['food_id'].tolist())))\n",
    "df_fndds.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_fndds.to_csv('../processed_data/fndds.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dietary Record Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "years = ['0304', '0506', '0708', '0910', '1112', '1314', '1516', '1718', '1720']\n",
    "year_char = 'C'\n",
    "type_dietary = 'dietary'\n",
    "df_IFF1 = concat_data_across_years(type_dietary, 'DR1IFF', years, year_char)\n",
    "df_IFF2 = concat_data_across_years(type_dietary, 'DR2IFF', years, year_char)\n",
    "\n",
    "# Food and nutrition data\n",
    "food_columns_1 = ['SEQN', 'food_id', 'DR1IGRMS',\n",
    " 'DR1IKCAL', 'DR1IPROT', 'DR1ICARB', 'DR1ISUGR', 'DR1IFIBE', 'DR1ITFAT',\n",
    " 'DR1ISFAT', 'DR1IMFAT', 'DR1IPFAT', 'DR1ICHOL', 'DR1IATOC', 'DR1IATOA',\n",
    " 'DR1IRET', 'DR1IVARA', 'DR1IACAR', 'DR1IBCAR', 'DR1ICRYP', 'DR1ILYCO',\n",
    " 'DR1ILZ', 'DR1IVB1', 'DR1IVB2', 'DR1INIAC', 'DR1IVB6', 'DR1IFOLA',\n",
    " 'DR1IFA', 'DR1IFF', 'DR1IFDFE', 'DR1ICHL', 'DR1IVB12', 'DR1IB12A',\n",
    " 'DR1IVC', 'DR1IVD', 'DR1IVK', 'DR1ICALC', 'DR1IPHOS', 'DR1IMAGN',\n",
    " 'DR1IIRON', 'DR1IZINC', 'DR1ICOPP', 'DR1ISODI', 'DR1IPOTA', 'DR1ISELE',\n",
    " 'DR1ICAFF', 'DR1ITHEO', 'DR1IALCO', 'DR1IMOIS'\n",
    "]\n",
    "food_columns_2 = ['SEQN', 'food_id', 'DR2IGRMS',\n",
    " 'DR2IKCAL', 'DR2IPROT', 'DR2ICARB', 'DR2ISUGR', 'DR2IFIBE', 'DR2ITFAT',\n",
    " 'DR2ISFAT', 'DR2IMFAT', 'DR2IPFAT', 'DR2ICHOL', 'DR2IATOC', 'DR2IATOA',\n",
    " 'DR2IRET', 'DR2IVARA', 'DR2IACAR', 'DR2IBCAR', 'DR2ICRYP', 'DR2ILYCO',\n",
    " 'DR2ILZ', 'DR2IVB1', 'DR2IVB2', 'DR2INIAC', 'DR2IVB6', 'DR2IFOLA',\n",
    " 'DR2IFA', 'DR2IFF', 'DR2IFDFE', 'DR2ICHL', 'DR2IVB12', 'DR2IB12A',\n",
    " 'DR2IVC', 'DR2IVD', 'DR2IVK', 'DR2ICALC', 'DR2IPHOS', 'DR2IMAGN',\n",
    " 'DR2IIRON', 'DR2IZINC', 'DR2ICOPP', 'DR2ISODI', 'DR2IPOTA', 'DR2ISELE',\n",
    " 'DR2ICAFF', 'DR2ITHEO', 'DR2IALCO', 'DR2IMOIS'\n",
    "]\n",
    "\n",
    "df_IFF1 = df_IFF1.rename(columns={'DR1IFDCD': 'food_id'})\n",
    "df_IFF1 = df_IFF1[food_columns_1].astype(float)\n",
    "df_IFF2 = df_IFF2.rename(columns={'DR2IFDCD': 'food_id'})\n",
    "df_IFF2 = df_IFF2[food_columns_2].astype(float)\n",
    "df_food  = pd.DataFrame(np.vstack((df_IFF1.to_numpy(), df_IFF2.to_numpy())), columns=df_IFF1.columns)\n",
    "\n",
    "df_IFF1 = df_IFF1[['SEQN', 'food_id']].astype(int).astype(str)\n",
    "df_IFF2 = df_IFF2[['SEQN', 'food_id']].astype(int).astype(str)\n",
    "df_IFF1['food_id'] = df_IFF1['food_id'].str.zfill(10)\n",
    "df_IFF2['food_id'] = df_IFF2['food_id'].str.zfill(10)\n",
    "df_food_user = pd.concat([df_IFF1, df_IFF2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SEQN</th>\n",
       "      <th>food_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>21005</td>\n",
       "      <td>0091745020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>21005</td>\n",
       "      <td>0092410710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>21005</td>\n",
       "      <td>0071201010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>21005</td>\n",
       "      <td>0025230230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>21005</td>\n",
       "      <td>0051301010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149490</th>\n",
       "      <td>124820</td>\n",
       "      <td>0027214100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149491</th>\n",
       "      <td>124820</td>\n",
       "      <td>0025210210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149492</th>\n",
       "      <td>124820</td>\n",
       "      <td>0071200100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149493</th>\n",
       "      <td>124820</td>\n",
       "      <td>0064104030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149494</th>\n",
       "      <td>124820</td>\n",
       "      <td>0057305100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2322627 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          SEQN     food_id\n",
       "0        21005  0091745020\n",
       "1        21005  0092410710\n",
       "2        21005  0071201010\n",
       "3        21005  0025230230\n",
       "4        21005  0051301010\n",
       "...        ...         ...\n",
       "149490  124820  0027214100\n",
       "149491  124820  0025210210\n",
       "149492  124820  0071200100\n",
       "149493  124820  0064104030\n",
       "149494  124820  0057305100\n",
       "\n",
       "[2322627 rows x 2 columns]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# This is the crosswalk between users and food records.\n",
    "df_food_user"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_food_user.to_csv('../processed_data/food_user.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a new DataFrame for the nutritional data\n",
    "df_nutrition = pd.DataFrame()\n",
    "df_nutrition['food_id'] = df_food['food_id'].unique()\n",
    "df_food = df_food.dropna(subset=['DR1IGRMS'])\n",
    "for col in df_food.columns.tolist()[3:]:\n",
    "    df_food[col] = df_food[col] / df_food['DR1IGRMS'] * 100\n",
    "\n",
    "df_food.drop(['SEQN', 'DR1IGRMS'], axis=1, inplace=True)\n",
    "\n",
    "df_food = df_food.groupby('food_id').mean().reset_index()\n",
    "df_food = df_food.fillna(0)\n",
    "df_food['food_id'] = df_food['food_id'].astype(int)\n",
    "df_nutrition = df_nutrition.merge(df_food, how='left', on='food_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>food_id</th>\n",
       "      <th>DR1IKCAL</th>\n",
       "      <th>DR1IPROT</th>\n",
       "      <th>DR1ICARB</th>\n",
       "      <th>DR1ISUGR</th>\n",
       "      <th>DR1IFIBE</th>\n",
       "      <th>DR1ITFAT</th>\n",
       "      <th>DR1ISFAT</th>\n",
       "      <th>DR1IMFAT</th>\n",
       "      <th>DR1IPFAT</th>\n",
       "      <th>...</th>\n",
       "      <th>DR1IIRON</th>\n",
       "      <th>DR1IZINC</th>\n",
       "      <th>DR1ICOPP</th>\n",
       "      <th>DR1ISODI</th>\n",
       "      <th>DR1IPOTA</th>\n",
       "      <th>DR1ISELE</th>\n",
       "      <th>DR1ICAFF</th>\n",
       "      <th>DR1ITHEO</th>\n",
       "      <th>DR1IALCO</th>\n",
       "      <th>DR1IMOIS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>91745020.0</td>\n",
       "      <td>395.278311</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>98.001075</td>\n",
       "      <td>62.916355</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.185781</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.306136</td>\n",
       "      <td>0.001037</td>\n",
       "      <td>0.028588</td>\n",
       "      <td>36.937549</td>\n",
       "      <td>3.733017</td>\n",
       "      <td>0.415312</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.307854</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>92410710.0</td>\n",
       "      <td>41.036190</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>10.600139</td>\n",
       "      <td>10.600139</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.050206</td>\n",
       "      <td>0.070027</td>\n",
       "      <td>0.006991</td>\n",
       "      <td>12.959341</td>\n",
       "      <td>0.997621</td>\n",
       "      <td>0.100043</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>89.300262</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>71201010.0</td>\n",
       "      <td>545.736044</td>\n",
       "      <td>6.562972</td>\n",
       "      <td>50.063185</td>\n",
       "      <td>1.741627</td>\n",
       "      <td>4.383135</td>\n",
       "      <td>37.155633</td>\n",
       "      <td>8.901914</td>\n",
       "      <td>11.665290</td>\n",
       "      <td>13.304546</td>\n",
       "      <td>...</td>\n",
       "      <td>1.613473</td>\n",
       "      <td>2.394710</td>\n",
       "      <td>0.397999</td>\n",
       "      <td>525.080821</td>\n",
       "      <td>1642.578659</td>\n",
       "      <td>8.109794</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.283774</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>25230230.0</td>\n",
       "      <td>109.660947</td>\n",
       "      <td>17.589882</td>\n",
       "      <td>1.129724</td>\n",
       "      <td>0.154148</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.054334</td>\n",
       "      <td>0.826198</td>\n",
       "      <td>1.196596</td>\n",
       "      <td>0.306546</td>\n",
       "      <td>...</td>\n",
       "      <td>0.670906</td>\n",
       "      <td>1.745734</td>\n",
       "      <td>0.249023</td>\n",
       "      <td>1160.934459</td>\n",
       "      <td>532.559195</td>\n",
       "      <td>27.556117</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>74.363413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>51301010.0</td>\n",
       "      <td>268.153759</td>\n",
       "      <td>10.461369</td>\n",
       "      <td>48.159033</td>\n",
       "      <td>5.827805</td>\n",
       "      <td>3.978369</td>\n",
       "      <td>3.783562</td>\n",
       "      <td>0.789484</td>\n",
       "      <td>0.818855</td>\n",
       "      <td>1.463109</td>\n",
       "      <td>...</td>\n",
       "      <td>3.492356</td>\n",
       "      <td>1.143217</td>\n",
       "      <td>0.163818</td>\n",
       "      <td>509.969638</td>\n",
       "      <td>175.926071</td>\n",
       "      <td>29.068944</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>35.407697</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 47 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      food_id    DR1IKCAL   DR1IPROT   DR1ICARB   DR1ISUGR  DR1IFIBE  \\\n",
       "0  91745020.0  395.278311   0.000000  98.001075  62.916355  0.000000   \n",
       "1  92410710.0   41.036190   0.000000  10.600139  10.600139  0.000000   \n",
       "2  71201010.0  545.736044   6.562972  50.063185   1.741627  4.383135   \n",
       "3  25230230.0  109.660947  17.589882   1.129724   0.154148  0.000000   \n",
       "4  51301010.0  268.153759  10.461369  48.159033   5.827805  3.978369   \n",
       "\n",
       "    DR1ITFAT  DR1ISFAT   DR1IMFAT   DR1IPFAT  ...  DR1IIRON  DR1IZINC  \\\n",
       "0   0.185781  0.000000   0.000000   0.000000  ...  0.306136  0.001037   \n",
       "1   0.000000  0.000000   0.000000   0.000000  ...  0.050206  0.070027   \n",
       "2  37.155633  8.901914  11.665290  13.304546  ...  1.613473  2.394710   \n",
       "3   3.054334  0.826198   1.196596   0.306546  ...  0.670906  1.745734   \n",
       "4   3.783562  0.789484   0.818855   1.463109  ...  3.492356  1.143217   \n",
       "\n",
       "   DR1ICOPP     DR1ISODI     DR1IPOTA   DR1ISELE  DR1ICAFF  DR1ITHEO  \\\n",
       "0  0.028588    36.937549     3.733017   0.415312       0.0       0.0   \n",
       "1  0.006991    12.959341     0.997621   0.100043       0.0       0.0   \n",
       "2  0.397999   525.080821  1642.578659   8.109794       0.0       0.0   \n",
       "3  0.249023  1160.934459   532.559195  27.556117       0.0       0.0   \n",
       "4  0.163818   509.969638   175.926071  29.068944       0.0       0.0   \n",
       "\n",
       "   DR1IALCO   DR1IMOIS  \n",
       "0       0.0   1.307854  \n",
       "1       0.0  89.300262  \n",
       "2       0.0   2.283774  \n",
       "3       0.0  74.363413  \n",
       "4       0.0  35.407697  \n",
       "\n",
       "[5 rows x 47 columns]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_nutrition.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "11338"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\"\n",
    "This is merely for information. Not used in the pipeline.\n",
    "\n",
    "We use the food code NHANES provided, which is more complete than FNDDS. For duplications, we also keep the latest records.\n",
    "In this way, every food users reported has its corresponding food description.\n",
    "We use this as the connections between users and food.\n",
    "\"\"\"\n",
    "\n",
    "food_dictionary = concat_data_across_years(type_dietary, 'DRXFCD', years, year_char)\n",
    "food_dictionary = food_dictionary.rename(columns={'DRXFDCD': 'food_id', 'DRXFCLD': 'food_desc'})\n",
    "\n",
    "food_dictionary = food_dictionary[['food_id', 'food_desc', 'years']]\n",
    "food_dictionary['food_id'] = food_dictionary['food_id'].astype(int)\n",
    "food_dictionary = food_dictionary.drop_duplicates(subset='food_id', keep='last')\n",
    "\n",
    "food_nhanes_have = set(food_dictionary['food_id'].tolist())\n",
    "len(food_nhanes_have)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# By now, we have three tables: \n",
    "# `df_fndds` is the table for connecting foods to ingredients and categories; \n",
    "# `df_food_user` is the table for connecting foods to users who consume them; \n",
    "# `df_nutrition` is the table for the foods and their nutritions per 100g."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_nutrition['food_id'] = df_nutrition['food_id'].astype(int).astype(str).str.zfill(10)\n",
    "df_nutrition = df_nutrition.set_index('food_id')\n",
    "df_nutrtion = df_nutrition.round(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DR1IKCAL</th>\n",
       "      <th>DR1IPROT</th>\n",
       "      <th>DR1ICARB</th>\n",
       "      <th>DR1ISUGR</th>\n",
       "      <th>DR1IFIBE</th>\n",
       "      <th>DR1ITFAT</th>\n",
       "      <th>DR1ISFAT</th>\n",
       "      <th>DR1IMFAT</th>\n",
       "      <th>DR1IPFAT</th>\n",
       "      <th>DR1ICHOL</th>\n",
       "      <th>...</th>\n",
       "      <th>DR1IIRON</th>\n",
       "      <th>DR1IZINC</th>\n",
       "      <th>DR1ICOPP</th>\n",
       "      <th>DR1ISODI</th>\n",
       "      <th>DR1IPOTA</th>\n",
       "      <th>DR1ISELE</th>\n",
       "      <th>DR1ICAFF</th>\n",
       "      <th>DR1ITHEO</th>\n",
       "      <th>DR1IALCO</th>\n",
       "      <th>DR1IMOIS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>9639.00</td>\n",
       "      <td>9639.00</td>\n",
       "      <td>9639.00</td>\n",
       "      <td>9639.00</td>\n",
       "      <td>9639.00</td>\n",
       "      <td>9639.00</td>\n",
       "      <td>9639.00</td>\n",
       "      <td>9639.00</td>\n",
       "      <td>9639.00</td>\n",
       "      <td>9639.00</td>\n",
       "      <td>...</td>\n",
       "      <td>9639.00</td>\n",
       "      <td>9639.00</td>\n",
       "      <td>9639.00</td>\n",
       "      <td>9639.00</td>\n",
       "      <td>9639.00</td>\n",
       "      <td>9639.00</td>\n",
       "      <td>9639.00</td>\n",
       "      <td>9639.00</td>\n",
       "      <td>9639.00</td>\n",
       "      <td>9639.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>192.52</td>\n",
       "      <td>8.12</td>\n",
       "      <td>21.37</td>\n",
       "      <td>7.79</td>\n",
       "      <td>1.87</td>\n",
       "      <td>8.52</td>\n",
       "      <td>2.65</td>\n",
       "      <td>3.12</td>\n",
       "      <td>2.03</td>\n",
       "      <td>29.86</td>\n",
       "      <td>...</td>\n",
       "      <td>1.84</td>\n",
       "      <td>1.31</td>\n",
       "      <td>0.14</td>\n",
       "      <td>338.78</td>\n",
       "      <td>216.74</td>\n",
       "      <td>11.75</td>\n",
       "      <td>2.47</td>\n",
       "      <td>5.28</td>\n",
       "      <td>0.13</td>\n",
       "      <td>60.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>139.39</td>\n",
       "      <td>8.02</td>\n",
       "      <td>22.09</td>\n",
       "      <td>13.03</td>\n",
       "      <td>2.77</td>\n",
       "      <td>10.66</td>\n",
       "      <td>3.85</td>\n",
       "      <td>4.54</td>\n",
       "      <td>3.65</td>\n",
       "      <td>67.46</td>\n",
       "      <td>...</td>\n",
       "      <td>3.89</td>\n",
       "      <td>3.06</td>\n",
       "      <td>0.36</td>\n",
       "      <td>417.81</td>\n",
       "      <td>204.00</td>\n",
       "      <td>24.16</td>\n",
       "      <td>72.69</td>\n",
       "      <td>37.48</td>\n",
       "      <td>1.55</td>\n",
       "      <td>27.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>77.92</td>\n",
       "      <td>2.05</td>\n",
       "      <td>6.21</td>\n",
       "      <td>0.94</td>\n",
       "      <td>0.21</td>\n",
       "      <td>1.94</td>\n",
       "      <td>0.44</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.32</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.47</td>\n",
       "      <td>0.33</td>\n",
       "      <td>0.05</td>\n",
       "      <td>135.81</td>\n",
       "      <td>117.61</td>\n",
       "      <td>1.23</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>46.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>162.24</td>\n",
       "      <td>5.63</td>\n",
       "      <td>13.95</td>\n",
       "      <td>2.64</td>\n",
       "      <td>1.19</td>\n",
       "      <td>5.01</td>\n",
       "      <td>1.40</td>\n",
       "      <td>1.69</td>\n",
       "      <td>1.03</td>\n",
       "      <td>4.78</td>\n",
       "      <td>...</td>\n",
       "      <td>1.07</td>\n",
       "      <td>0.68</td>\n",
       "      <td>0.08</td>\n",
       "      <td>294.58</td>\n",
       "      <td>185.66</td>\n",
       "      <td>6.64</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>66.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>272.69</td>\n",
       "      <td>11.60</td>\n",
       "      <td>26.10</td>\n",
       "      <td>7.62</td>\n",
       "      <td>2.38</td>\n",
       "      <td>12.00</td>\n",
       "      <td>3.53</td>\n",
       "      <td>4.34</td>\n",
       "      <td>2.40</td>\n",
       "      <td>38.42</td>\n",
       "      <td>...</td>\n",
       "      <td>1.87</td>\n",
       "      <td>1.38</td>\n",
       "      <td>0.13</td>\n",
       "      <td>453.61</td>\n",
       "      <td>262.68</td>\n",
       "      <td>18.66</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>81.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>901.29</td>\n",
       "      <td>78.13</td>\n",
       "      <td>100.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>77.26</td>\n",
       "      <td>100.00</td>\n",
       "      <td>82.87</td>\n",
       "      <td>75.22</td>\n",
       "      <td>66.85</td>\n",
       "      <td>3074.13</td>\n",
       "      <td>...</td>\n",
       "      <td>64.18</td>\n",
       "      <td>98.86</td>\n",
       "      <td>14.47</td>\n",
       "      <td>19645.56</td>\n",
       "      <td>6022.51</td>\n",
       "      <td>1917.13</td>\n",
       "      <td>4491.99</td>\n",
       "      <td>2050.64</td>\n",
       "      <td>36.66</td>\n",
       "      <td>99.98</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8 rows × 46 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       DR1IKCAL  DR1IPROT  DR1ICARB  DR1ISUGR  DR1IFIBE  DR1ITFAT  DR1ISFAT  \\\n",
       "count   9639.00   9639.00   9639.00   9639.00   9639.00   9639.00   9639.00   \n",
       "mean     192.52      8.12     21.37      7.79      1.87      8.52      2.65   \n",
       "std      139.39      8.02     22.09     13.03      2.77     10.66      3.85   \n",
       "min        0.00      0.00      0.00      0.00      0.00      0.00      0.00   \n",
       "25%       77.92      2.05      6.21      0.94      0.21      1.94      0.44   \n",
       "50%      162.24      5.63     13.95      2.64      1.19      5.01      1.40   \n",
       "75%      272.69     11.60     26.10      7.62      2.38     12.00      3.53   \n",
       "max      901.29     78.13    100.00    100.00     77.26    100.00     82.87   \n",
       "\n",
       "       DR1IMFAT  DR1IPFAT  DR1ICHOL  ...  DR1IIRON  DR1IZINC  DR1ICOPP  \\\n",
       "count   9639.00   9639.00   9639.00  ...   9639.00   9639.00   9639.00   \n",
       "mean       3.12      2.03     29.86  ...      1.84      1.31      0.14   \n",
       "std        4.54      3.65     67.46  ...      3.89      3.06      0.36   \n",
       "min        0.00      0.00      0.00  ...      0.00      0.00      0.00   \n",
       "25%        0.50      0.32      0.00  ...      0.47      0.33      0.05   \n",
       "50%        1.69      1.03      4.78  ...      1.07      0.68      0.08   \n",
       "75%        4.34      2.40     38.42  ...      1.87      1.38      0.13   \n",
       "max       75.22     66.85   3074.13  ...     64.18     98.86     14.47   \n",
       "\n",
       "       DR1ISODI  DR1IPOTA  DR1ISELE  DR1ICAFF  DR1ITHEO  DR1IALCO  DR1IMOIS  \n",
       "count   9639.00   9639.00   9639.00   9639.00   9639.00   9639.00   9639.00  \n",
       "mean     338.78    216.74     11.75      2.47      5.28      0.13     60.21  \n",
       "std      417.81    204.00     24.16     72.69     37.48      1.55     27.08  \n",
       "min        0.00      0.00      0.00      0.00      0.00      0.00      0.00  \n",
       "25%      135.81    117.61      1.23      0.00      0.00      0.00     46.56  \n",
       "50%      294.58    185.66      6.64      0.00      0.00      0.00     66.90  \n",
       "75%      453.61    262.68     18.66      0.00      0.00      0.00     81.81  \n",
       "max    19645.56   6022.51   1917.13   4491.99   2050.64     36.66     99.98  \n",
       "\n",
       "[8 rows x 46 columns]"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_nutrition.describe().round(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Tagging the food items"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "nutrition_mapping = {'DR1IKCAL': 'calorie', 'DR1IPROT': 'protein', 'DR1ICARB': 'carb', 'DR1ISUGR': 'sugar', 'DR1IFIBE': 'fiber', \n",
    "                     'DR1ISFAT': 'saturated_fat', 'DR1ICHOL': 'cholesterol', 'DR1ISODI': 'sodium', 'DR1ICALC': 'calcium', 'DR1IPHOS': 'phosphorus',\n",
    "                     'DR1IPOTA': 'potassium', 'DR1IIRON': 'iron', 'DR1IFA': 'folic_acid', 'DR1IVC': 'vitamin_c', 'DR1IVD': 'vitamin_d', 'DR1IVB12': 'vitamin_b12'\n",
    "                     }\n",
    "nutrition_columns = ['DR1IKCAL', 'DR1IPROT', 'DR1ICARB', 'DR1ISUGR', 'DR1IFIBE', 'DR1ISFAT', 'DR1ICHOL', \n",
    "                    'DR1ISODI', 'DR1ICALC', 'DR1IPHOS', 'DR1IPOTA', 'DR1IIRON', 'DR1IFA', 'DR1IVC', 'DR1IVD', 'DR1IVB12']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "thresholds = {\n",
    "    'calorie': {'low': 40, 'high': 225},\n",
    "    'protein': {'low': 10, 'high': 15},\n",
    "    'carb': {'low': 55, 'high': 75},\n",
    "    'sugar': {'low': 5, 'high': 22.5},\n",
    "    'fiber': {'low': 3, 'high': 6},\n",
    "    'saturated_fat': {'low': 1.5, 'high': 5},\n",
    "    'cholesterol': {'low': 20, 'high': 40},\n",
    "    'sodium': {'low': 120, 'high': 200},\n",
    "    'calcium': {'low': 0, 'high': 150},\n",
    "    'phosphorus': {'low': 0, 'high': 105},\n",
    "    'potassium': {'low': 0, 'high': 525},\n",
    "    'iron': {'low': 0, 'high': 3.3},\n",
    "    'folic_acid': {'low': 0, 'high': 60},\n",
    "    'vitamin_c': {'low': 0, 'high': 15},\n",
    "    'vitamin_d': {'low': 0, 'high': 2.25},\n",
    "    'vitamin_b12': {'low': 0, 'high': 0.36},\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_nutrition = df_nutrition[nutrition_columns]\n",
    "df_nutrition = df_nutrition.rename(columns=nutrition_mapping)\n",
    "for nutrient, cols in nutrition_mapping.items():\n",
    "    low_col = f'low_{cols}'\n",
    "    high_col = f'high_{cols}'\n",
    "    \n",
    "    df_nutrition[low_col] = df_nutrition[cols].apply(lambda x: 1 if x <= thresholds[cols]['low'] else 0)\n",
    "    df_nutrition[high_col] = df_nutrition[cols].apply(lambda x: 1 if x > thresholds[cols]['high'] else 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>calorie</th>\n",
       "      <td>9639.0</td>\n",
       "      <td>192.516941</td>\n",
       "      <td>139.394545</td>\n",
       "      <td>0.0</td>\n",
       "      <td>77.916779</td>\n",
       "      <td>162.235930</td>\n",
       "      <td>272.686853</td>\n",
       "      <td>901.287554</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>protein</th>\n",
       "      <td>9639.0</td>\n",
       "      <td>8.121850</td>\n",
       "      <td>8.022546</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.050089</td>\n",
       "      <td>5.630233</td>\n",
       "      <td>11.602435</td>\n",
       "      <td>78.132389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>carb</th>\n",
       "      <td>9639.0</td>\n",
       "      <td>21.368806</td>\n",
       "      <td>22.089875</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.212551</td>\n",
       "      <td>13.948457</td>\n",
       "      <td>26.104188</td>\n",
       "      <td>100.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sugar</th>\n",
       "      <td>9639.0</td>\n",
       "      <td>7.789790</td>\n",
       "      <td>13.031943</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.936700</td>\n",
       "      <td>2.638770</td>\n",
       "      <td>7.615897</td>\n",
       "      <td>100.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fiber</th>\n",
       "      <td>9639.0</td>\n",
       "      <td>1.866523</td>\n",
       "      <td>2.768045</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.214974</td>\n",
       "      <td>1.191127</td>\n",
       "      <td>2.375233</td>\n",
       "      <td>77.262443</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>saturated_fat</th>\n",
       "      <td>9639.0</td>\n",
       "      <td>2.651805</td>\n",
       "      <td>3.845347</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.442835</td>\n",
       "      <td>1.398990</td>\n",
       "      <td>3.529069</td>\n",
       "      <td>82.865372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cholesterol</th>\n",
       "      <td>9639.0</td>\n",
       "      <td>29.863797</td>\n",
       "      <td>67.463802</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.780511</td>\n",
       "      <td>38.420255</td>\n",
       "      <td>3074.130506</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sodium</th>\n",
       "      <td>9639.0</td>\n",
       "      <td>338.780392</td>\n",
       "      <td>417.813502</td>\n",
       "      <td>0.0</td>\n",
       "      <td>135.810877</td>\n",
       "      <td>294.582629</td>\n",
       "      <td>453.609211</td>\n",
       "      <td>19645.555556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>calcium</th>\n",
       "      <td>9639.0</td>\n",
       "      <td>73.661708</td>\n",
       "      <td>125.405424</td>\n",
       "      <td>0.0</td>\n",
       "      <td>14.830429</td>\n",
       "      <td>36.502458</td>\n",
       "      <td>92.839061</td>\n",
       "      <td>3433.481160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>phosphorus</th>\n",
       "      <td>9639.0</td>\n",
       "      <td>126.676617</td>\n",
       "      <td>124.631671</td>\n",
       "      <td>0.0</td>\n",
       "      <td>43.016101</td>\n",
       "      <td>98.367389</td>\n",
       "      <td>177.459677</td>\n",
       "      <td>1676.785714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>potassium</th>\n",
       "      <td>9639.0</td>\n",
       "      <td>216.744112</td>\n",
       "      <td>203.997842</td>\n",
       "      <td>0.0</td>\n",
       "      <td>117.612987</td>\n",
       "      <td>185.662974</td>\n",
       "      <td>262.684023</td>\n",
       "      <td>6022.510823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>iron</th>\n",
       "      <td>9639.0</td>\n",
       "      <td>1.842895</td>\n",
       "      <td>3.888377</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.471973</td>\n",
       "      <td>1.071525</td>\n",
       "      <td>1.872686</td>\n",
       "      <td>64.175652</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>folic_acid</th>\n",
       "      <td>9639.0</td>\n",
       "      <td>22.768286</td>\n",
       "      <td>96.990296</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>15.024510</td>\n",
       "      <td>1841.452991</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>vitamin_c</th>\n",
       "      <td>9639.0</td>\n",
       "      <td>6.596594</td>\n",
       "      <td>18.424257</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.943203</td>\n",
       "      <td>5.959188</td>\n",
       "      <td>559.952262</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>vitamin_d</th>\n",
       "      <td>9639.0</td>\n",
       "      <td>0.460600</td>\n",
       "      <td>1.600704</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.020752</td>\n",
       "      <td>0.301825</td>\n",
       "      <td>36.283186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>vitamin_b12</th>\n",
       "      <td>9639.0</td>\n",
       "      <td>0.627403</td>\n",
       "      <td>2.197226</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.152343</td>\n",
       "      <td>0.485145</td>\n",
       "      <td>82.438245</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>low_calorie</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.079357</td>\n",
       "      <td>0.270309</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>high_calorie</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.353423</td>\n",
       "      <td>0.478057</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>low_protein</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.691390</td>\n",
       "      <td>0.461944</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>high_protein</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.165456</td>\n",
       "      <td>0.371611</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>low_carb</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.887344</td>\n",
       "      <td>0.316188</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>high_carb</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.044087</td>\n",
       "      <td>0.205299</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>low_sugar</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.665871</td>\n",
       "      <td>0.471709</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>high_sugar</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.103527</td>\n",
       "      <td>0.304662</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>low_fiber</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.828112</td>\n",
       "      <td>0.377303</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>high_fiber</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.060685</td>\n",
       "      <td>0.238763</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>low_saturated_fat</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.522822</td>\n",
       "      <td>0.499505</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>high_saturated_fat</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.154564</td>\n",
       "      <td>0.361508</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>low_cholesterol</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.637448</td>\n",
       "      <td>0.480762</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>high_cholesterol</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.241286</td>\n",
       "      <td>0.427886</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>low_sodium</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.240456</td>\n",
       "      <td>0.427383</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>high_sodium</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.667531</td>\n",
       "      <td>0.471122</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>low_calcium</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.013278</td>\n",
       "      <td>0.114469</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>high_calcium</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.109440</td>\n",
       "      <td>0.312206</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>low_phosphorus</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.015041</td>\n",
       "      <td>0.121724</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>high_phosphorus</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.469087</td>\n",
       "      <td>0.499069</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>low_potassium</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.005498</td>\n",
       "      <td>0.073948</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>high_potassium</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.035373</td>\n",
       "      <td>0.184731</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>low_iron</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.013589</td>\n",
       "      <td>0.115784</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>high_iron</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.086618</td>\n",
       "      <td>0.281289</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>low_folic_acid</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.593050</td>\n",
       "      <td>0.491291</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>high_folic_acid</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.051245</td>\n",
       "      <td>0.220508</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>low_vitamin_c</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.257573</td>\n",
       "      <td>0.437320</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>high_vitamin_c</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.114212</td>\n",
       "      <td>0.318085</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>low_vitamin_d</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.443568</td>\n",
       "      <td>0.496831</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>high_vitamin_d</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.039523</td>\n",
       "      <td>0.194845</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>low_vitamin_b12</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.297718</td>\n",
       "      <td>0.457278</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>high_vitamin_b12</th>\n",
       "      <td>9640.0</td>\n",
       "      <td>0.319191</td>\n",
       "      <td>0.466187</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     count        mean         std  min         25%  \\\n",
       "calorie             9639.0  192.516941  139.394545  0.0   77.916779   \n",
       "protein             9639.0    8.121850    8.022546  0.0    2.050089   \n",
       "carb                9639.0   21.368806   22.089875  0.0    6.212551   \n",
       "sugar               9639.0    7.789790   13.031943  0.0    0.936700   \n",
       "fiber               9639.0    1.866523    2.768045  0.0    0.214974   \n",
       "saturated_fat       9639.0    2.651805    3.845347  0.0    0.442835   \n",
       "cholesterol         9639.0   29.863797   67.463802  0.0    0.000000   \n",
       "sodium              9639.0  338.780392  417.813502  0.0  135.810877   \n",
       "calcium             9639.0   73.661708  125.405424  0.0   14.830429   \n",
       "phosphorus          9639.0  126.676617  124.631671  0.0   43.016101   \n",
       "potassium           9639.0  216.744112  203.997842  0.0  117.612987   \n",
       "iron                9639.0    1.842895    3.888377  0.0    0.471973   \n",
       "folic_acid          9639.0   22.768286   96.990296  0.0    0.000000   \n",
       "vitamin_c           9639.0    6.596594   18.424257  0.0    0.000000   \n",
       "vitamin_d           9639.0    0.460600    1.600704  0.0    0.000000   \n",
       "vitamin_b12         9639.0    0.627403    2.197226  0.0    0.000000   \n",
       "low_calorie         9640.0    0.079357    0.270309  0.0    0.000000   \n",
       "high_calorie        9640.0    0.353423    0.478057  0.0    0.000000   \n",
       "low_protein         9640.0    0.691390    0.461944  0.0    0.000000   \n",
       "high_protein        9640.0    0.165456    0.371611  0.0    0.000000   \n",
       "low_carb            9640.0    0.887344    0.316188  0.0    1.000000   \n",
       "high_carb           9640.0    0.044087    0.205299  0.0    0.000000   \n",
       "low_sugar           9640.0    0.665871    0.471709  0.0    0.000000   \n",
       "high_sugar          9640.0    0.103527    0.304662  0.0    0.000000   \n",
       "low_fiber           9640.0    0.828112    0.377303  0.0    1.000000   \n",
       "high_fiber          9640.0    0.060685    0.238763  0.0    0.000000   \n",
       "low_saturated_fat   9640.0    0.522822    0.499505  0.0    0.000000   \n",
       "high_saturated_fat  9640.0    0.154564    0.361508  0.0    0.000000   \n",
       "low_cholesterol     9640.0    0.637448    0.480762  0.0    0.000000   \n",
       "high_cholesterol    9640.0    0.241286    0.427886  0.0    0.000000   \n",
       "low_sodium          9640.0    0.240456    0.427383  0.0    0.000000   \n",
       "high_sodium         9640.0    0.667531    0.471122  0.0    0.000000   \n",
       "low_calcium         9640.0    0.013278    0.114469  0.0    0.000000   \n",
       "high_calcium        9640.0    0.109440    0.312206  0.0    0.000000   \n",
       "low_phosphorus      9640.0    0.015041    0.121724  0.0    0.000000   \n",
       "high_phosphorus     9640.0    0.469087    0.499069  0.0    0.000000   \n",
       "low_potassium       9640.0    0.005498    0.073948  0.0    0.000000   \n",
       "high_potassium      9640.0    0.035373    0.184731  0.0    0.000000   \n",
       "low_iron            9640.0    0.013589    0.115784  0.0    0.000000   \n",
       "high_iron           9640.0    0.086618    0.281289  0.0    0.000000   \n",
       "low_folic_acid      9640.0    0.593050    0.491291  0.0    0.000000   \n",
       "high_folic_acid     9640.0    0.051245    0.220508  0.0    0.000000   \n",
       "low_vitamin_c       9640.0    0.257573    0.437320  0.0    0.000000   \n",
       "high_vitamin_c      9640.0    0.114212    0.318085  0.0    0.000000   \n",
       "low_vitamin_d       9640.0    0.443568    0.496831  0.0    0.000000   \n",
       "high_vitamin_d      9640.0    0.039523    0.194845  0.0    0.000000   \n",
       "low_vitamin_b12     9640.0    0.297718    0.457278  0.0    0.000000   \n",
       "high_vitamin_b12    9640.0    0.319191    0.466187  0.0    0.000000   \n",
       "\n",
       "                           50%         75%           max  \n",
       "calorie             162.235930  272.686853    901.287554  \n",
       "protein               5.630233   11.602435     78.132389  \n",
       "carb                 13.948457   26.104188    100.000000  \n",
       "sugar                 2.638770    7.615897    100.000000  \n",
       "fiber                 1.191127    2.375233     77.262443  \n",
       "saturated_fat         1.398990    3.529069     82.865372  \n",
       "cholesterol           4.780511   38.420255   3074.130506  \n",
       "sodium              294.582629  453.609211  19645.555556  \n",
       "calcium              36.502458   92.839061   3433.481160  \n",
       "phosphorus           98.367389  177.459677   1676.785714  \n",
       "potassium           185.662974  262.684023   6022.510823  \n",
       "iron                  1.071525    1.872686     64.175652  \n",
       "folic_acid            0.000000   15.024510   1841.452991  \n",
       "vitamin_c             0.943203    5.959188    559.952262  \n",
       "vitamin_d             0.020752    0.301825     36.283186  \n",
       "vitamin_b12           0.152343    0.485145     82.438245  \n",
       "low_calorie           0.000000    0.000000      1.000000  \n",
       "high_calorie          0.000000    1.000000      1.000000  \n",
       "low_protein           1.000000    1.000000      1.000000  \n",
       "high_protein          0.000000    0.000000      1.000000  \n",
       "low_carb              1.000000    1.000000      1.000000  \n",
       "high_carb             0.000000    0.000000      1.000000  \n",
       "low_sugar             1.000000    1.000000      1.000000  \n",
       "high_sugar            0.000000    0.000000      1.000000  \n",
       "low_fiber             1.000000    1.000000      1.000000  \n",
       "high_fiber            0.000000    0.000000      1.000000  \n",
       "low_saturated_fat     1.000000    1.000000      1.000000  \n",
       "high_saturated_fat    0.000000    0.000000      1.000000  \n",
       "low_cholesterol       1.000000    1.000000      1.000000  \n",
       "high_cholesterol      0.000000    0.000000      1.000000  \n",
       "low_sodium            0.000000    0.000000      1.000000  \n",
       "high_sodium           1.000000    1.000000      1.000000  \n",
       "low_calcium           0.000000    0.000000      1.000000  \n",
       "high_calcium          0.000000    0.000000      1.000000  \n",
       "low_phosphorus        0.000000    0.000000      1.000000  \n",
       "high_phosphorus       0.000000    1.000000      1.000000  \n",
       "low_potassium         0.000000    0.000000      1.000000  \n",
       "high_potassium        0.000000    0.000000      1.000000  \n",
       "low_iron              0.000000    0.000000      1.000000  \n",
       "high_iron             0.000000    0.000000      1.000000  \n",
       "low_folic_acid        1.000000    1.000000      1.000000  \n",
       "high_folic_acid       0.000000    0.000000      1.000000  \n",
       "low_vitamin_c         0.000000    1.000000      1.000000  \n",
       "high_vitamin_c        0.000000    0.000000      1.000000  \n",
       "low_vitamin_d         0.000000    1.000000      1.000000  \n",
       "high_vitamin_d        0.000000    0.000000      1.000000  \n",
       "low_vitamin_b12       0.000000    1.000000      1.000000  \n",
       "high_vitamin_b12      0.000000    1.000000      1.000000  "
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Have a look at the data\n",
    "df_nutrition.describe().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_nutrition.to_csv('../processed_data/food_tagging.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "FRS",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.18"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
